package cn.com.jsepc.rep.report;


import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.Hashtable;
import javax.servlet.ServletException;



import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import cn.com.jsepc.rep.catalog.CatalogDAO;
import cn.com.jsepc.rep.comm.Common;
import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.ReportVO;

import com.jarp.db.DAO;

public class zhibiao_manage {

	public synchronized String addzhibiao(String itemJsonInfo)
			throws ServletException {
		try {
			JSONObject jsonObject = new JSONObject(itemJsonInfo);
			DAO dao = new DAO();
			ReportVO vo = new ReportVO();
			vo.setRepcode(jsonObject.getString("bianma_zeng"));
			String seq_bianma = "select * from tb_report where repcode = '"+ vo.getRepcode() +"'";
			Vector v = dao.getDataSet(ConstUtil.DBNAME, seq_bianma);
			if(v != null){
				System.out.println("数据库中有此编码");
				return "haven";
			} else {
				
			vo.setRepname(jsonObject.getString("mingcheng_zeng"));
			String seq_mingcheng = "select * from tb_report where repname = '"+ vo.getRepname() +"'";
			Vector v1 = dao.getDataSet(ConstUtil.DBNAME, seq_mingcheng);
			if(v1 != null){
				System.out.println("数据库中有此名称");
				return "haven1";
			} else {			
			vo.setCataid(jsonObject.getString("fenlei_zeng_hidden"));
			vo.setRepdesc(jsonObject.getString("hanyi_zeng"));
			vo.setDesc(jsonObject.getString("miaoshu_zeng"));
			String jd = jsonObject.getString("jingdu_zeng");
			if (jd.equals("")) {
				vo.setDataprec("0");
			}else {
				vo.setDataprec(jsonObject.getString("jingdu_zeng"));
			}
			vo.setDataunit(jsonObject.getString("danwei_zeng"));
			vo.setDepartment(jsonObject.getString("bumen_zeng"));
			vo.setSourcetype(jsonObject.getString("laiyuan_zeng"));
			vo.setDatatype(jsonObject.getString("koujing_zeng"));
			vo.setUpdatetime(jsonObject.getString("zhouqi_zeng"));
			vo.setRepvaluecol(jsonObject.getString("zhibiaozhi_zeng"));
			vo.setIssum(jsonObject.getString("juhe_zeng"));
			vo.setIsvalid(jsonObject.getString("keyong_zeng"));
			
			Common comm = new Common();
			String repidval = comm.getSeqStr("SEQ_REPORT");

			String seq = "insert into tb_report(" + "repid, " + "repcode, "
					+ "repname, " + "cataid, " + "repdesc, " + "caldesc, "
					+ "dataprec, " + "dataunit, " + "department, "
					+ "sourcetype, " + "datatype, "+ "updatetime," + "Repvaluecol," + "Issum," + "isvalid) values("
					+ repidval + ",'"
					+ vo.getRepcode()
					+ "','"
					+ vo.getRepname()
					+ "',"
					+ vo.getCataid()
					+ ",'"
					+ vo.getRepdesc()
					+ "','"
					+ vo.getDesc()
					+ "',"
					+ vo.getDataprec()
					+ ",'"
					+ vo.getDataunit()
					+ "','"
					+ vo.getDepartment()
					+ "',"
					+ vo.getSourcetype()
					+ ","
					+ vo.getDatatype()
					+ ","
					+ vo.getUpdatetime() 
					+ ",'"
					+ vo.getRepvaluecol() 
					+"',"
					+ vo.getIssum()
					+","
					+ vo.getIsvalid()
					+ ")";
			System.out.println(seq);
			int bRet = dao.execCommand(ConstUtil.DBNAME, seq);
			if (bRet == 1) {
				System.out.println("添加成功");
				return "ok";
			} else {
				System.out.println("添加失败");
			}
			} }
		} catch (RuntimeException e) {
			e.printStackTrace();
			return "错误！";
		}
		return "";
	}

	public synchronized String updatezhibiao(String itemJsonInfo)
			throws ServletException {
		try {
			JSONObject jsonObject = new JSONObject(itemJsonInfo);
			DAO dao = new DAO();
			ReportVO vo = new ReportVO();
			vo.setRepid(jsonObject.getString("bianhao_update"));
			vo.setRepcode(jsonObject.getString("bianma_update"));
			String seq_bianma = "select * from tb_report where repcode = '"+ vo.getRepcode() +"' and repid != " + vo.getRepid();
//			System.out.println(seq_bianma);
			Vector v_ = dao.getDataSet(ConstUtil.DBNAME, seq_bianma);
			if(v_ != null){
				System.out.println("数据库中有此编码");
				return "haven";
			} else {
			vo.setRepname(jsonObject.getString("mingcheng_update"));
			String seq_mingcheng = "select * from tb_report where repname = '"+ vo.getRepname() +"' and repid != " + vo.getRepid();
			Vector v_1 = dao.getDataSet(ConstUtil.DBNAME, seq_mingcheng);
			if(v_1 != null){
				System.out.println("数据库中有此名称");
				return "haven1";
			} else {
			vo.setCataid(jsonObject.getString("fenlei_update"));
			vo.setRepdesc(jsonObject.getString("hanyi_update"));
			vo.setDesc(jsonObject.getString("miaoshu_update"));
			String jd_ = jsonObject.getString("jingdu_update");
			if (jd_.equals("")) {
				vo.setDataprec("0");
			}else {
				vo.setDataprec(jsonObject.getString("jingdu_update"));
			}
			vo.setDataunit(jsonObject.getString("danwei_update"));
			vo.setDepartment(jsonObject.getString("bumen_update"));
			vo.setSourcetype(jsonObject.getString("laiyuan_update"));
			vo.setDatatype(jsonObject.getString("koujing_update"));
			vo.setUpdatetime(jsonObject.getString("zhouqi_update"));
			vo.setRepvaluecol(jsonObject.getString("zhibiaozhi_update"));
			vo.setIssum(jsonObject.getString("juhe_update"));
			vo.setIsvalid(jsonObject.getString("keyong_update"));

			String sql = "update tb_report set repcode='" + vo.getRepcode()
					+ "',repname='" + vo.getRepname() + "',cataid="
					+ vo.getCataid() + ",repdesc='" + vo.getRepdesc()
					+ "',caldesc='" + vo.getDesc() + "',dataprec="
					+ vo.getDataprec() + ",dataunit='" + vo.getDataunit()
					+ "',department='" + vo.getDepartment() + "',sourcetype="
					+ vo.getSourcetype() + ",datatype=" + vo.getDatatype()
					+ ",updatetime=" + vo.getUpdatetime()+ ",repvaluecol='" + vo.getRepvaluecol()+ "',Issum=" + vo.getIssum() +",isvalid=" + vo.getIsvalid()  +" where repid = "
					+ vo.getRepid() + "";
//			System.out.println(sql);
			int bRet = dao.execCommand(ConstUtil.DBNAME, sql);
			if (bRet == 1) {
				System.out.println("修改成功");
			} else {
				System.out.println("修改失败");
			}
			}}
		} catch (RuntimeException e) {
			return "ko";
		}
		return "ok";
	}

	public synchronized String delzhibiao(String itemJsonInfo)
	throws ServletException {
try {
	ArrayList<String> del_record = new ArrayList<String>();
	JSONObject jsonObject = new JSONObject(itemJsonInfo);
	DAO dao = new DAO();
	String sql_del_reportid_info = "delete from tb_report where REPID = " + jsonObject.getInt("bianhao");
	del_record.add(sql_del_reportid_info);

	String seq = "select * from TB_REP_DEMENSION where REPID = "+ jsonObject.getInt("bianhao");
				Vector v = dao.getDataSet(ConstUtil.DBNAME, seq);
				if(v != null){
					System.out.println("此数据有维度关联");
					return "have_guanlian";
				} 
				
	String seq_cs = "select * from TB_REP_PARA where REPID = "+ jsonObject.getInt("bianhao");
				Vector v_cs = dao.getDataSet(ConstUtil.DBNAME, seq_cs);
				if(v_cs != null){
					System.out.println("此数据有关联参数");
					return "have_guanlian_cs";
				} 
	
 	Boolean bRet = dao.execCommand(ConstUtil.DBNAME, del_record);
	if (bRet == true) {
		System.out.println("删除成功");
		return "ok";
	} else {
		System.out.println("删除失败");
		return "wrong";
	}

} catch (RuntimeException e) {
	e.printStackTrace();
	return "wrong";
}
}

	// 加载数据
	public String getGrid(String start, String limit, String KeyWord_0,
			String KeyWord_1, String KeyWord_2, String KeyWord_3,
			String KeyWord_4,String KeyWord_5,String KeyWord_6) {
		String res = "";
		DAO dao = new DAO();
		try {
			List<ReportVO> lstResults = new ArrayList<ReportVO>();
			int iStart = Integer.parseInt(start);
			int iPageSize = Integer.parseInt(limit);
			int iPage =  iStart/iPageSize + 1;
			StringBuffer str = new StringBuffer();
			boolean bEscape = false;
			if (KeyWord_0!=null && KeyWord_0.indexOf("_")>=0){
				bEscape = true;
				KeyWord_0 = KeyWord_0.replaceAll("_","|_");
			}
			//通过限定条件，获取用户所需数据
			str.append("select a.*,b.CATANAME,c.NAME DEPTNAME from TB_REPORT a ");
			str.append(" left join tb_catalog b on b.CATAID=a.CATAID ");
			str.append(" left join TB_CORP_DEPT c on c.ID=a.DEPARTMENT ");
			str.append(" where 1 = 1 ");
			//点击树的情况
			if(KeyWord_5 != null && !KeyWord_5.equals(""))
			{
//				System.out.println("您点击了树：树的id为KeyWord_5");
				//点击树根节点的情况：查询所有
				if(KeyWord_5.equals("0")){
//					System.out.println("您点击了树的根节点");
					
					if (KeyWord_0 != null && !KeyWord_0.equals("")) {			
						str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
						if (bEscape){
							str.append(" escape '|' ");
						}
					}
					if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {	
						str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "'");
					}
					if (KeyWord_2 != null && !KeyWord_2.equals("")) {		
						if(KeyWord_2.equals("3")){
						}else{
							str.append(" and a.SOURCETYPE = " + KeyWord_2);
						}
					}
					if (KeyWord_3 != null && !KeyWord_3.equals("")) {			
						if(KeyWord_3.equals("6")){
						}else{
							str.append(" and a.DATATYPE = " + KeyWord_3);
						}
					}
					if (KeyWord_4 != null && !KeyWord_4.equals("")) {		
						if(KeyWord_4.equals("7")){
						}else{
							str.append(" and a.UPDATETIME = " + KeyWord_4);
						}
					}
					if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
						str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
					}
			}
				//点击树子节点的情况：与查询条件关联查询
				else{
//					System.out.println("您点击了树的叶子节点");
					
				if (KeyWord_0 != null && !KeyWord_0.equals("")) {				//KeyWord_0：指标名称
					str.append(" and a.REPNAME like '%" + KeyWord_0 + "%'");
					if (bEscape){
						str.append(" escape '|' ");
					}
				}
				if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {		//KeyWord_1:归口部门
					str.append(" and a.DEPARTMENT = '" + KeyWord_1 + "' ");
				}
				if (KeyWord_2 != null && !KeyWord_2.equals("")) {					//KeyWord_2:数据来源
					if(KeyWord_2.equals("3")){
					}else{
						str.append(" and a.SOURCETYPE = " + KeyWord_2 );
					}
				}
//				if (KeyWord_3 != null && !KeyWord_3.equals("")) {					//KeyWord_3:数据口径
//					if(KeyWord_3.equals("6")){
//					}else{
//						str.append(" and a.DATATYPE = " + KeyWord_3 );
//					}
//				}
				if (KeyWord_4 != null && !KeyWord_4.equals("")) {						//KeyWord_4:更新周期
					if(KeyWord_4.equals("7")){
					}else{
						str.append(" and a.UPDATETIME = " + KeyWord_4 );				
					}
				}
				if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
					str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
				}
				if (KeyWord_5 != null && !KeyWord_5.equals("")) {
					String catid = new CatalogDAO().getAllCatatID(KeyWord_5);
					str.append(" and a.CATAID in ( " + catid+") ");
				}
				
			}
				}
			//未点击树的情况
			else{
				if (KeyWord_0 != null && !KeyWord_0.equals("")) {
					str.append(" and REPNAME like '%" + KeyWord_0 + "%'");
					if (bEscape){
						str.append(" escape '|' ");
					}
				}
				if (KeyWord_1 != null && !KeyWord_1.equals("") && !KeyWord_1.equals("101")) {
					str.append(" and DEPARTMENT = '" + KeyWord_1 + "'");
				}
				if (KeyWord_2 != null && !KeyWord_2.equals("")) {
					if(KeyWord_2.equals("3")){
					}else{
						str.append(" and SOURCETYPE = " + KeyWord_2);
					}
				}
//				if (KeyWord_3 != null && !KeyWord_3.equals("")) {
//					if(KeyWord_3.equals("6")){
//					}else{
//						str.append(" and DATATYPE = " + KeyWord_3);
//					}
//				}
				if (KeyWord_4 != null && !KeyWord_4.equals("")) {
					if(KeyWord_4.equals("7")){
					}else{
						str.append(" and UPDATETIME = " + KeyWord_4);
					}
				}
				if (KeyWord_6 != null && !KeyWord_6.equals("")) {		
					str.append(" and a.REPCODE like '%" + KeyWord_6.toUpperCase() + "%'");
				}
			}
			str.append(" order by a.cataid");
			System.out.println(str.toString());
			Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString(),iPage,iPageSize);
			if (v != null) {
				for (int i = 0; i < v.size(); i++) {
					Hashtable hashRows = (Hashtable) v.get(i);
					ReportVO repVO = new ReportVO();
					repVO.setRepid((String) hashRows.get("REPID"));
					repVO.setRepcode((String) hashRows.get("REPCODE"));
					repVO.setRepname((String) hashRows.get("REPNAME"));
					repVO.setCataid((String) hashRows.get("CATAID"));
					repVO.setCataname((String) hashRows.get("CATANAME"));
					repVO.setRepdesc((String) hashRows.get("REPDESC"));
					repVO.setDesc((String) hashRows.get("CALDESC"));
					repVO.setDataprec((String) hashRows.get("DATAPREC"));
					repVO.setDataunit((String) hashRows.get("DATAUNIT"));
					repVO.setDepartment((String) hashRows.get("DEPTNAME"));
					repVO.setDepartmentid((String) hashRows.get("DEPARTMENT"));
					repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
					repVO.setDatatype((String) hashRows.get("DATATYPE"));
					repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
					repVO.setReffact((String) hashRows.get("REFFACT"));
					repVO.setRepvaluecol((String) hashRows.get("REPVALUECOL"));
					repVO.setIssum((String) hashRows.get("ISSUM"));
					repVO.setIsvalid((String) hashRows.get("ISVALID"));
					lstResults.add(repVO);//将数据放置结果集中
				}
			}
			
			JSONArray json = new JSONArray();
			StringBuffer str1 = new StringBuffer();
			str1.append("select count(1) CNT from (");
			str1.append(str);
			str1.append(" ) z ");
			String totalCount = dao.getDataValue(ConstUtil.DBNAME, str1.toString());
			json = JSONArray.fromObject(lstResults);//向前台以json形式返回结果集
			res = "{totalCount:" + totalCount + ",data:" + json.toString()
					+ "}";
		} catch (Exception ex) {
			ex.printStackTrace();
			System.out.println("Exception.");
		}
//		System.out.println("*********************结束本次查询操作*************************");
		return res;
	}
}
